SQL迴圈實作 -4.關聯分析的處理工廠2

2019鐵人賽

張小馬~

2018-10-24 18:17:40

4197 瀏覽

分享至

0.ithelp_asso_source

要做到購物籃分析，實際上只需要二個欄位：

一、籃子或消費者：兩者的差異在於，籃子是以訂單為主、消費者是以人為主；以訂單來看，哪二種商品最常被一起購買，或是，以人來看，哪二種商品最常被一起購買。本次使用的檔案是以消費者(人)為主，如果想嘗試使用訂單來做，那就是把這個消費者姓名欄位(name)換成訂單編號即可。

二、商品或商品類別：這取決於這份分析是想要看到【單商品(最小單位)之間】的關聯，還是看到【商品種類之間】的關聯。本次使用的檔案是以商品類別(category)來做，組合比較少，較容易說明。

換句話說，整份原始資料ithelp_asso_source的概念就是，這個人，消費了那些商品類別。而我們想要分析：哪二種商品類別，很常一起被人所購買。

1.ithelp_asso_t1_category

t1要做的事情

首先，必須針對要分析的商品做以下四件事情：
一、流水編號(category_no)：從1開始的編號，是為了讓後面SQL迴圈，可以從1開始跑。
二、被幾個人消費過(cnt)：這邊的消費指的是【不重複的人數】，換句話說，如果一個人消費了三次相同的商品類別，這邊也只記為1次。如果你想像的是要記為3次，那你是以訂單去做的購物籃分析(當然也可以)，而不是以人。
三、資料中總共有幾個人(total)：非常重要的數字，雖然在此資料中看起來是被擴充的(意即每列重複相同內容，且加總無意義)，但為了方便之後使用，必須這個步驟就先做出來。
四、被人選購的比例(percentage)：若總共有100人，其中60人購買過該商品類別，比例就是60%，這邊是以去掉百分比的概念呈現60，當然你也可以寫成0.6，看個人習慣。(爆雷一下，這個數字是lift值的分母喔~)

SQL如下，我會說明各層在幹嘛，但更細節的SQL寫法，我就不多說了，這邊當作大家具備基礎SQL能力。再次強調，小馬習慣以一層層洋蔥般包覆的寫法，乍看之下很複雜，事實上只要先找到最內層，把內層得到的data想像出來，往外一層一層處理，就很有邏輯、變得簡單了。

由內往外

LJA層：從資料中把唯一的name取出來。
LJ層：把name當key，【total】是不重複的name總數。
SO層：原始資料
A層：把SO和LJ層透過name合併，等於先把total建回原始資料裡。
B層：針對category去群集，把【cnt】做出來。
最外層：做出專屬於category的【category_no】和【percentage】。專屬意指一對一資料。

DROP TABLE IF EXISTS ithelp_asso_t1_category; 
CREATE TABLE ithelp_asso_t1_category as 
select 
row_number() over (Partition by 1 order by category) category_no,
B.category,
B.cnt,
B.total,
ROUND(B.cnt::numeric*100/B.total::numeric,2) percentage
--------以下B層--------
from( 
select 
A.category,
A.total,
SUM(1) cnt
--------以下A層--------
from(
select SO.*,LJ.total
from ithelp_asso_source SO
--------以下Left Join層--------
LEFT JOIN(
select
name,
SUM(1) over (PARTITION BY 1) total
from(
select name
from ithelp_asso_source
group by name
) LJA --LJ層裡面包了一個LJ的A層
) LJ
--------以上Left Join層--------
on SO.name = LJ.name
) A
--------以上A層--------
group by category,total
) B
--------以上B層--------
; 
select * from ithelp_asso_t1_category

透過上面這串SQL，你可以得到與這份相同的Excel檔案(.xlsx)。

補充一下，會看到我上面的SQL中，同樣用了LEFT JOIN，這不免讓人困惑，明明小馬前面一直說用OVER (PARTITION BY)就好，弄到現在，到底什麼時候要用JOIN，什麼時候用OP？

這份SQL檔(可用記事本開啟)供各位參考，與上面的SQL有相同結果。你會發現我在不用JOIN的情況下，必須寫三個OP，才能做出【total】這個數字，而這正是慣用寫法那篇有提到過的：

一次join可以OK的，用OP必須拆成三段以上時，效能就會比較差；但大多數時候，一段OP能完成的事情，會比一次join的效能來得好。

這也是在此寫成LEFT JOIN的原因。

除此之外，四、被人選購的比例(percentage)，雖然是Lift值的分母，但實際上後面我們算Lift值時並不會直接用到這個數字，換句話說，這數字雖然在t1被我們寫出來，但之後不會用到。

「那...為什麼還要寫出來？」

如果你是一個實作者，或同行，我們經常會遇到這樣的狀況：大家(長官、商品經理、分析人員等等)並不需要真的走到做完關聯分析那步，而僅僅單純想知道，到底某項商品被人選購的比例是多少？那就簡單了，t1做完，就能回答這個問題。因此，縱使之後關聯分析用不上，但在這階段先把這數字做出來，絕對是有備無患。

2.ithelp_asso_t2_source_no

t2要做的事

把category_no(迴圈最重要的變數) join回原始資料。這步驟蠻單純的，熟練後可以省略此步驟直接寫進第3步驟裡，但小馬我仍建議將此步驟獨立出來，因為這樣能讓自己更清楚，我們接著往後，是針對這樣的一份資料(ithelp_asso_t2_source_no)做迴圈。

DROP TABLE IF EXISTS ithelp_asso_t2_source_no; 
CREATE TABLE ithelp_asso_t2_source_no as 
select
s.*,
n.category_no
from ithelp_asso_source s 
left join ithelp_asso_t1_category n 
on s.category = n.category
;
select * from ithelp_asso_t2_source_no

上面t2做出的結果如Excel檔案(.xlsx)，我們接著要針對這份資料做迴圈。

3.ithelp_asso_t3_setof

t3要做的事

我們要將步驟2產出的ithelp_asso_t2_source_no，做出其中一個組合，舉例如下面這一筆，我只取編號2和編號6的商品，做出這二商品的【共選次數】：

由內往外

如果你有自己的慣用寫法能做到，當然可以用自己的寫法，以下分享我個人的寫法：

A層：針對編號2和編號6的商品，列出全部【只要購買過編號2或編號6其中之一】的人，計算他們購買的商品數，同時購買二商品的人，其cnt=2，只購買其中一項商品的，cnt=1。
B層：只保留cnt=2的人
C層：計算剩下來的人數，就是會同時購買編號2和編號6的人數，我命名為【共選次數】，與共選人數相同(編號2和編號6的商品同時被幾個不同的人一起購買)。把一些註解放上去，包括【商品1編號】(是2)、【商品2編號】(是6)、【商品組合】(2_6)。

這邊可能大家會擔心，我直接指定2和6，給這個欄位，可以嗎？
可以，因為之後只要寫著2和6的，都會被我們改成迴圈會去跑的【變數/參數】。

最外層：想像一下最後，如果只看到2_6，會困惑【所以2_6到底是哪兩個商品啊？】所以我們再把ithelp_asso_t1_category拿來用，join兩次，把2和6的名稱給帶出來。
得到【商品1名稱】、【商品2名稱】、【組合名稱】。

同時，這邊跑出的7個欄位，也是迴圈一旦跑完，會產出來的所有欄位，就是這7個。

以下SQL語法：

DROP FUNCTION IF EXISTS ithelp_asso_fun1(r int, s int);
DROP FUNCTION IF EXISTS ithelp_asso_fun2(u int, v int);
DROP TABLE IF EXISTS ithelp_asso_t3_setof; 
CREATE TABLE ithelp_asso_t3_setof as 

select
C.共選次數,
p1.category 商品1名稱, 
p2.category 商品2名稱, 
p1.category||'_'||p2.category 組合名稱,
C.商品1編號, 
C.商品2編號, 
C.組合編號

from(
select
SUM(1) 共選次數, 
2 "商品1編號", 
6 "商品2編號", 
2||'_'||6 組合編號
from(
select A.* 
from(
select
name, 
SUM(1) cnt
from ithelp_asso_t2_source_no
where category_no in (2,6) 
group by name 
) A 
where cnt =2 
) B 
group by cnt
) C 
LEFT JOIN ithelp_asso_t1_category p1 
on C.商品1編號 = p1.category_no
LEFT JOIN ithelp_asso_t1_category p2 
on C.商品2編號 = p2.category_no
; 
select * from ithelp_asso_t3_setof

做出結果如Excel檔案(.xlsx)只有一筆資料，前面有提到過，t3目的不在為了跑出這1筆資料，而是為了建置好那7個欄位。